# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json
import pymysql
import os
import pymongo
from . settings import mongo_host,mongo_port,mongo_db_name,mongo_db_collection

class DBPipeline(object):

    def __init__(self):

        # self.file = open('teacher.json', 'w',encoding="utf-8")
        #打开数据库连接
        self.db = pymysql.connect(host="47.90.103.72", user="root",
                             password="Shaoqiang88..", db="guns", port=3306, charset='utf8')

        # 使用cursor()方法获取操作游标
        self.cur = self.db.cursor()

    def process_item(self, item, spider):
        # content = json.dumps(dict(item),ensure_ascii=False)+"\n"
        # self.file.write(content)

        if item['type'] == 'bifenGet':
            sql_insert = "insert into soccer_result \
                              (league_name, match_time, home_team, guest_team, home_goal, guest_goal, \
                              full_goal, final_result, shuju_urls, yazhou_urls, ouzhou_urls, daxiao_urls, \
                              game_id, half_goal, bs_jiaozhan, bs_home_zhanji, bs_guest_zhanji, avwinc2, avdrawc2,\
                               avlostc2, avwinj2, avdrawj2, avlostj2, yazhouj, yazhouc, daxiaoj, daxiaoc, date_time ) \
                                values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
            tmpSql = sql_insert % (item['leagueName'], item['matchTime'], item['homeTeam'], item['guestTeam'], item['homeGoal'], item['guestGoal'],
                                   item['fullGoal'], item['finalResult'], item['shuju_urls'], item['yazhou_urls'], item['ouzhou_urls'], item['daxiao_urls'],
                                   item['gameId'], item['halfGoal'], item['bs_jiaozhan'], item['bs_home_zhanji'], item['bs_guest_zhanji'], item['avwinc2'], item['avdrawc2'],
                                   item['avlostc2'], item['avwinj2'], item['avdrawj2'], item['avlostj2'], item['yazhouj'], item['yazhouc'], item['daxiaoj'], item['daxiaoc'], item['dateTime'])

            print("\ntmpSql", tmpSql)

            self.cur.execute(tmpSql)

        if item['type'] == 'matchGet':
            sql_insert = "insert into soccer_toyday_match \
                              (league_name, match_time, home_team, guest_team,  \
                             shuju_urls, yazhou_urls, ouzhou_urls, daxiao_urls, \
                              game_id, bs_jiaozhan, bs_home_zhanji, bs_guest_zhanji, avwinc2, avdrawc2,\
                               avlostc2, avwinj2, avdrawj2, avlostj2, yazhouj, yazhouc, daxiaoj, daxiaoc, date_time ) \
                                values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
            tmpSql = sql_insert % (
            item['leagueName'], item['matchTime'], item['homeTeam'], item['guestTeam'],
            item['shuju_urls'], item['yazhou_urls'], item['ouzhou_urls'],
            item['daxiao_urls'],
            item['gameId'],  item['bs_jiaozhan'], item['bs_home_zhanji'], item['bs_guest_zhanji'],
            item['avwinc2'], item['avdrawc2'],
            item['avlostc2'], item['avwinj2'], item['avdrawj2'], item['avlostj2'], item['yazhouj'], item['yazhouc'],
            item['daxiaoj'], item['daxiaoc'], item['dateTime'])

            print("\ntmpSql", tmpSql)

            self.cur.execute(tmpSql)

        if item['type'] == 'oddGet':
            sql_insert = "insert into soccer_odd \
                              (gameId,  avwinc2, avdrawc2,\
                               avlostc2, avwinj2, avdrawj2, avlostj2, yazhouj, yazhouc, daxiaoj, daxiaoc, dateTime ) \
                                values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
            tmpSql = sql_insert % (
            item['gameId'],
            item['avwinc2'], item['avdrawc2'],
            item['avlostc2'], item['avwinj2'], item['avdrawj2'], item['avlostj2'], item['yazhouj'], item['yazhouc'],
            item['daxiaoj'], item['daxiaoc'], item['dateTime'])

            print("\ntmpSql", tmpSql)

            self.cur.execute(tmpSql)
        # 提交
        self.db.commit()

        return item

    def close_spider(self, spider):
        self.db.close()


    # def process_item(self, item, spider):
    #     return item


class SoccerMongoPipeline(object):

    def __init__(self):
        host = mongo_host
        port = mongo_port
        dbname = mongo_db_name
        sheetname = mongo_db_collection
        client = pymongo.MongoClient(host=host,port=port)
        mydb = client[dbname]
        self.post = mydb[sheetname]

    def process_item(self, item, spider):

        data = dict(item)
        self.post.insert(data)
        return item